package com.zhengjiang.code.utils;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

import java.sql.*;

public class SSHUtils {

    private static String user = "root";
    private static String host = "120.55.37.78";
    private static int port = 22;
    private static String password = "kp]YM^G{qfJAxQ0F&NjZ*dQrLP&K|M";

    static Integer lport = 13306;//本地端口
    static String rhost  = "localhost";//远程数据库服务器
    static int rport = 3306;//远程数据库服务端口

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        sshRun();
    }

    public static void sshRun() {
        JSch jsch = new JSch();
        Session session = null;
        try {
            session = jsch.getSession(user, host, port);
            session.setPassword(password);
            session.setConfig("StrictHostKeyChecking", "no");
            // step1：建立ssh连接
            session.connect();
            System.out.println(session.getServerVersion());//这里打印SSH服务器版本信息
            //step2： 设置SSH本地端口转发，本地转发到远程
            int assinged_port = session.setPortForwardingL("localhost", lport, host, rport);
            System.out.println("localhost:" + assinged_port + " -> " + rhost + ":" + rport);

            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:"+assinged_port +"/hamsv4_njgy?useSSL=false";
            //第三步：通过DriverManager的getconnection方法获取数据连接
            Connection connection = DriverManager.getConnection(url,"njgydev","1qaz@WSX");
            System.out.println(connection);

            String sql = "select * from t_building_area";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                String address = resultSet.getString("address");
                String buildingArea = resultSet.getString("building_area");
                System.out.println("address:"+address+"-"+buildingArea);
            }
            preparedStatement.close();
            resultSet.close();
            connection.close();

        } catch (Exception e) {
            if (null != session) {
                //关闭ssh连接
                session.disconnect();
            }
            e.printStackTrace();
        }finally {
            if (null != session) {
                //关闭ssh连接
                session.disconnect();
            }
        }
    }
}
